#!/bin/bash
if [ -n "$1" ]
then
	do_date=$1
else
	do_date= $(date -d yesterday +%F) 
fi

echo 当前要操作的日期是$do_date

dim_user_zip="
    insert overwrite table dim_user_zip partition (dt='9999-12-31')
select
    data.id,
       data.login_name,
       data.nick_name,
       md5(data.passwd),
       md5(data.real_name),
       md5(data.phone_num),
       md5(data.email),
       data.head_img,
       data.user_level,
       data.birthday,
       data.gender,
       data.create_time,
       data.operate_time,
       data.status,
       '2020-06-14' start_date,
        '9999-12-31' end_date
from ods_user_info_inc
where dt='2022-07-04'
and type='bootstrap-insert';
"
dim_test_paper_full="
insert overwrite table dim_test_paper_full partition (dt = '$do_date')
select
        a.id,
        a.paper_title,
        a.course_id,
        a.create_time,
        a.update_time,
        a.publisher_id,
        a.deleted,
        b.paper_id,
        b.question_id
from
(
    select id,
           paper_title,
           course_id,
           create_time,
           update_time,
           publisher_id,
           deleted
    from ods_test_paper_full
    where dt = '$do_date'
) a
left join
(
    select  id,
            paper_id,
            question_id
    from ods_test_paper_question_full
    where dt = '$do_date'
) b
on a.id = b.id;
"
dim_province_full="
insert overwrite table dim_province_full
partition(dt='2022-07-04')
select
       id,
       name,
       region_id,
       area_code,
       iso_code,
       iso_3166_2
    from ods_base_province_full
    where dt='2022-07-04';
"
dim_course_info_full="
with course as (
    select id,
           course_name,
           course_slogan,
           course_cover_url,
           subject_id,
           teacher,
           publisher_id,
           chapter_num,
           origin_price,
           reduce_amount,
           actual_price,
           course_introduce,
           create_time,
           update_time,
           deleted,
           dt
    from ods_course_info_full
    where dt = '$do_date'
),subject as(
    select id,
           subject_name,
           category_id
    from ods_base_subject_info_full
    where dt = '$do_date'
),category as (
    select  id,
            category_name
    from ods_base_category_info_full
    where dt = '$do_date'
)
insert overwrite table dim_course_info_full partition (dt='2022-07-05')
select
    course.`id`         ,
    `course_name`         ,
    `course_slogan`       ,
    `course_cover_url`  ,
    `subject_id`  ,
    `subject_name` ,
    `category_id` ,
    `category_name` ,
    `teacher`  ,
    `publisher_id`  ,
    `chapter_num`  ,
    `origin_price` ,
    `reduce_amount`  ,
    `actual_price`  ,
    `course_introduce`  ,
    `create_time`   ,
    `update_time`  ,
    `deleted`
from course
left join subject
on subject.id = subject_id
left join category
on category.id = subject.category_id;
"


dim_chapter_info_full = "
with ci as (
    select
            id,
            chapter_name,
            course_id,
            video_id,
            create_time,
            update_time
    from ods_chapter_info_full
    where dt = '2022-07-04'
),cp as (
    select
           data.user_id,
           data.position_sec,
           data.course_id
    from ods_user_chapter_process_inc
    where dt = '2022-07-04'
),com_info as (
    select
           data.comment_txt,
           data.course_id
    from ods_comment_info_inc
    where dt = '2022-07-04'
),vf as (
    select
           video_name,
           during_sec,
           video_status,
           video_size,
           video_url,
           version_id,
           chapter_id
    from ods_video_info_full
    where dt = '2022-07-04'
)

insert overwrite table dim_chapter_info_full partition (dt = '2022-07-04' )

select
       id,
       chapter_name,
       ci.course_id,
       video_id,
       create_time,
       update_time,
       chapter_id,
       user_id,
       position_sec,
       comment_txt,
       video_name,
       during_sec,
       video_status,
       video_size,
       video_url,
       version_id
from  ci left join cp on ci.course_id = cp.course_id
left join  com_info on ci.course_id = com_info.course_id
left join vf on ci.id = vf.chapter_id;
"


case $2 in
"dim_user_zip")
	hive --database gmall -e "$dim_user_zip"
	;;
"dim_province_full")
	hive --database gmall -e "$dim_province_full"
	;;
"dim_course_info_full")
	hive --database gmall -e "$dim_course_info_full"
	;;
"dim_chapter_info_full")
	hive --database gmall -e "$dim_chapter_info_full"
	;;
"all")
	hive --database gmall -e "$dim_user_zip$dim_test_paper_full$dim_course_info_full$dim_chapter_info_full"
	;;
esac